"""
Case Type   : 临时视图
Case Name   : 修改在范围分区表中创建视图
Create At   : 2024/10/17
Owner       : zhanghuan96
Description :
    1.创建用户，创建范围分区表，并插入数据
    2.创建临时视图指定用户
    3.查询临时视图schema
    4.创建用户
    5.修改临时视图的用户
    6.创建普通视图不指定用户
    7.查询普通视图schema
    8.修改普通视图的用户
    9.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.报错
    8.成功
    9.成功
History     :
"""

import os
import unittest

import py_opengauss
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class ListPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.user_node = Node('PrimaryDbBmode')
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.constant = Constant()
        self.u_name = 'u_grammar_0023'
        self.u_name_01 = 'u_grammar_0023_01'
        self.table_name = 't_grammar_0023'
        self.v_name = 'v_grammar_0023'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        step = '--step1:创建用户，创建范围分区表，并插入数据 expect:成功--'
        self.log.info(step)
        sql_cmd = f'''drop user if exists {self.u_name};
        create user {self.u_name} password '{macro.COMMON_PASSWD}';
        grant all privileges to {self.u_name};
        drop table if exists {self.table_name};
        create table {self.table_name} (c1 int not null,c2 numeric,c3 text)
        partition by range(c1)
        (partition p1 values less than(100),
        partition p2 values less than (500));
        insert into {self.table_name} values(100,3.14,'嘿嘿'),
        (200,'123.456','开门'),(50,2356,'芝麻');'''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('CREATE ROLE', result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertIn('INSERT 0 3', result)

        step = '--step2:创建临时视图指定用户expect:成功--'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        create_result = self.db1.query(f'''
        create or replace definer={self.u_name} temp view {self.v_name}(a,b,c)\
         with(security_barrier=true)as select c1,c2,c3 from \
        {self.table_name} partition(p2);''')
        self.log.info(create_result)
        self.assertIn('CREATE VIEW', create_result)

        step = '--step3:查询临时视图schema expect:成功--'
        self.log.info(step)
        sch_result = self.db1.query(f'''select nspname from pg_namespace \
        where oid = (select relnamespace from pg_class where \
        relname = '{self.v_name}');''')
        self.log.info(sch_result)
        query_res = sch_result[0][0]
        self.log.info(query_res)

        step = '--step4:创建用户expect:成功--'
        self.log.info(step)
        ddl_sql = f'''drop user if exists {self.u_name_01};'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        ddl_sql = f'''create user {self.u_name_01} password \
        '{macro.COMMON_PASSWD}';'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('CREATE ROLE', result)

        step = '--step5:修改临时视图的用户expect:成功--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name_01} view \
        {query_res}.{self.v_name}(a,b,c) as select c1,c2,c3 from \
        {self.table_name} partition(p2);'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('ALTER VIEW', result)

        step = '--step6:创建普通视图不指定用户expect:成功--'
        self.log.info(step)
        ddl_sql = f'''create or replace view {self.v_name}_01
        as select * from {self.table_name} partition(p1) where c1!=0;'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('CREATE VIEW', result)

        step = '--step7:查询普通视图schema expect:成功--'
        self.log.info(step)
        sch_result1 = self.db1.query(f'''select nspname from pg_namespace \
        where oid = (select relnamespace from pg_class where \
        relname = '{self.v_name}_01');''')
        self.log.info(sch_result1)
        query_res1 = sch_result1[0][0]
        self.log.info(query_res1)

        step = '--step8:修改普通视图的用户expect:成功--'
        self.log.info(step)
        ddl_sql = f'''alter definer={self.u_name} view \
              {query_res1}.{self.v_name}_01 as select * from {self.table_name} \
              partition(p1) where c1!=0;'''
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn('ALTER VIEW', result)

    def tearDown(self):
        step = '--step9:恢复环境,关闭session,删除测试表及用户--'
        self.log.info(step)
        self.db1.close()
        result1 = self.primary_sh.execut_db_sql(f'drop view if exists '
                                                f'{self.v_name}_01;')
        self.log.info(result1)
        result2 = self.primary_sh.execut_db_sql(f'drop user {self.u_name};'
                                                f'drop user {self.u_name_01};')
        self.log.info(result2)
        result3 = self.primary_sh.execut_db_sql(f'drop table if exists '
                                                f'{self.table_name};')
        self.log.info(result3)
        self.assertEqual(2, result2.count('DROP ROLE'), "执行失败:" + step)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result3, "执行失败:" + step)
        self.log.info(f'-----{os.path.basename(__file__)} end--')
